package com.zr.gdms.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.gdms.dao.TeacherDao;
import com.zr.gdms.model.Teacher;
import com.zr.gdms.util.JDBCUtil;
/**
 * 管理员管理教师逻辑实现层
 * @author 李培宁
 *
 */
public class TeacherDaoImpl implements TeacherDao{

	@Override
	public int findTeachersCount() {
		int tcount = 0;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(teacher.tid) AS tcount");
		sql.append(" FROM teacher");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				tcount = rs.getInt("tcount");
			}
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return tcount;
	}

	@Override
	public List<Teacher> findAllTeachers(int page, int rows) {
		List<Teacher> teachers = new ArrayList<Teacher>();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT*FROM teacher");
		sql.append(" LIMIT ?,?");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, page);
			pst.setInt(2, rows);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				Teacher t = new Teacher();
				t.setTid(rs.getInt("tid"));
				t.setTname(rs.getString("tname"));
				t.setTage(rs.getString("tage"));
				t.setTdepart(rs.getString("tdepart"));
				t.setTprofe(rs.getString("tprofe"));
				t.setTsex(rs.getString("tsex"));
				t.setTtel(rs.getString("ttel"));
				teachers.add(t);
			}
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		
		return teachers;
	}

	@Override
	public boolean insertTeacher(String tname,String tage,String tdepart,String tprofe,String tsex,String ttel) {
		boolean flag = false;
		StringBuffer  sql = new StringBuffer();
		sql.append(" INSERT INTO teacher(teacher.tname,teacher.tage,teacher.tdepart,teacher.tprofe,teacher.tsex,teacher.ttel) VALUES (?,?,?,?,?,?)");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, tname);
			pst.setString(2, tage);
			pst.setString(3, tdepart);
			pst.setString(4, tprofe);
			pst.setString(5, tsex);
			pst.setString(6, ttel);
			int i = pst.executeUpdate();
			if (i>0) {
				flag = true;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public boolean updateTeacher(String tname,String tage,String tdepart,String tprofe,String tsex,String ttel,int tid) {
		boolean flag = false;
		StringBuffer sql = new StringBuffer();
		sql.append(" UPDATE teacher SET teacher.tname=?,teacher.tage=?,teacher.tdepart=?,teacher.tprofe=?,teacher.tsex=?,teacher.ttel=?");
		sql.append(" WHERE teacher.tid = ?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, tname);
			pst.setString(2, tage);
			pst.setString(3, tdepart);
			pst.setString(4, tprofe);
			pst.setString(5, tsex);
			pst.setString(6, ttel);
			pst.setInt(7, tid);
			int i = pst.executeUpdate();
			if (i>0) {
				flag = true;
			}
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public boolean deleteTeacher(String appstate, String apptype) {
		boolean flag = false;
		StringBuffer sql =  new StringBuffer();
		sql.append(" DELETE teacher,application");
		sql.append(" FROM teacher");
		sql.append(" INNER JOIN application");
		sql.append(" ON teacher.tid = application.tid");
		sql.append(" WHERE application.appstate=? AND application.apptype=?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, appstate);
			pst.setString(2, apptype);
			int i = pst.executeUpdate();
			if (i>0) {
				flag = true;
			}
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public List<Teacher> searchTeachers(int page, int rows, int tid, String tname) {
		List<Teacher> teachers = new ArrayList<Teacher>();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT*FROM teacher");
		sql.append(" WHERE teacher.tid = ? OR teacher.tname LIKE ?");
		sql.append(" LIMIT ?,?");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, tid);
			pst.setString(2, "%"+tname+"%");
			pst.setInt(3, (page-1)*rows);
			pst.setInt(4, rows);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				Teacher t = new Teacher();
				t.setTid(rs.getInt("tid"));
				t.setTname(rs.getString("tname"));
				t.setTage(rs.getString("tage"));
				t.setTdepart(rs.getString("tdepart"));
				t.setTprofe(rs.getString("tprofe"));
				t.setTsex(rs.getString("tsex"));
				t.setTtel(rs.getString("ttel"));
				teachers.add(t);
			}
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return teachers;
	}
}
